﻿using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Transactions;
using T.ServiceComponent.I18N.SPI;
using T.FAS.Runtime.Base.Common;
using T.FAS.Runtime.Term.Entity;
using T.ServiceComponent.DAC.Service;
using T.ServiceComponent.DataAccessEx.Core;

namespace T.FAS.Runtime.Term.Dao
{
    public class PageTermMappingDao : DaoService<PageTermMapping>
    {
        public PageTermMappingDao() :
           base()
        {
            DBName = "TFAS";
        }
        public override string TableName => "page_termmapping";
        /// <summary>
        /// 获取公共语言翻译
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> GetCommonTranslatedTerms(string i18NID)
        {
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();
            paramList.Add(DB.MakeInParam("@i18NID", TDbDataType.VarChar, i18NID));
            var sql = $@"select trans.Code,term.OriginalText,item.TranslateText,item.CustomText 
                                    from Page_TermMapping trans
                                    join I18N_Term term on trans.TermID = term.ID
                                    join I18N_TermItems item on term.ID = item.TermID
                                    where trans.SourceType=2 and trans.TermUseType=0 and item.Language=@i18NID";
            return GetTranslatedTerms(paramList, sql);
        }
        /// <summary>
        /// 界面翻译项
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> GetTranslatedTerms(string i18NID, string pageMetaDataID)
        {
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();
            paramList.Add(DB.MakeInParam("@i18NID", TDbDataType.VarChar, i18NID));
            paramList.Add(DB.MakeInParam("@pageMetaDataID", TDbDataType.VarChar, pageMetaDataID));
            var sql = $@"select trans.Code,term.OriginalText,item.TranslateText,item.CustomText 
                                    from Page_TermMapping trans
                                    join I18N_Term term on trans.TermID = term.ID
                                    join I18N_TermItems item on term.ID = item.TermID
                                    join (select MetaDataID from FAS_PageRefSchema where PageMetaDataID = @pageMetaDataID union all select @pageMetaDataID MetaDataID) meta on trans.SourceID = meta.MetaDataID 
                                    where trans.SourceType=2 and trans.TermUseType=2 and item.Language=@i18NID and (trans.CustomTermID is null or trans.CustomTermID = '')
                                    union all
                                    select trans.Code,term.OriginalText,item.TranslateText,item.CustomText 
                                    from Page_TermMapping trans
                                    join I18N_Term term on trans.CustomTermID = term.ID
                                    join I18N_TermItems item on term.ID = item.TermID
                                    join (select MetaDataID from FAS_PageRefSchema where PageMetaDataID = @pageMetaDataID union all select @pageMetaDataID MetaDataID) meta on trans.SourceID = meta.MetaDataID 
                                    where trans.SourceType=2 and trans.TermUseType=2 and item.Language=@i18NID and trans.CustomTermID is not null and trans.CustomTermID != ''";
            return GetTranslatedTerms(paramList, sql);
        }
        /// <summary>
        /// 获取翻译项
        /// </summary>
        /// <param name="paramList"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private Dictionary<string, string> GetTranslatedTerms(List<IDbDataParameter> paramList, string sql)
        {
            Dictionary<string, string> translatedTerms = new Dictionary<string, string>();
            var ds = DB.ExecuteDataSet(sql, paramList.ToArray());
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var row = ds.Tables[0].Rows[i];
                    string code = Convert.ToString(row["Code"]);
                    string originalText = Convert.ToString(row["OriginalText"]);
                    string translateText = Convert.ToString(row["TranslateText"]);
                    string customText = Convert.ToString(row["CustomText"]);
                    if (!string.IsNullOrEmpty(customText))
                        translatedTerms[code] = customText;
                    else if (!string.IsNullOrEmpty(translateText))
                        translatedTerms[code] = translateText;
                    else
                        translatedTerms[code] = originalText;
                }
            }
            return translatedTerms;
        }/// <summary>
         /// 获取语言下所有界面
         /// </summary>
         /// <returns></returns>
        public List<PageRefInstance> GetAllPageRefInstances()
        {
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();
            var result = new List<PageRefInstance>();
            var sql = "select ID,Code from fas_pagemetadata";
            var ds = DB.ExecuteDataSet(sql, paramList.ToArray());
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var row = ds.Tables[0].Rows[i];
                    var instance = new PageRefInstance();
                    instance.PageMetaDataID = Convert.ToString(row["ID"]);
                    instance.PageMetaDataCode = Convert.ToString(row["Code"]);
                    result.Add(instance);
                }
            }
            return result;
        }
        /// <summary>
        /// 获取语言下所有界面
        /// </summary>
        /// <returns></returns>
        public List<PageRefInstance> GetPageRefInstancesBySourceID(List<string> sourceIDs)
        {
            var result = new List<PageRefInstance>();
            if (sourceIDs?.Count > 0)
            {
                string metaIds = GetIDFilter(sourceIDs);
                List<IDbDataParameter> paramList = new List<IDbDataParameter>();
                var sql = $@"select distinct meta.ID,meta.Code from fas_pagemetadata meta
                                    left join fas_pagerefschema refschema on meta.id = refschema.PageMetaDataID
                                    where meta.ID in ({metaIds}) or refschema.MetaDataID in ({metaIds})";
                var ds = DB.ExecuteDataSet(sql, paramList.ToArray());
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        var row = ds.Tables[0].Rows[i];
                        var instance = new PageRefInstance();
                        instance.PageMetaDataID = Convert.ToString(row["ID"]);
                        instance.PageMetaDataCode = Convert.ToString(row["Code"]);
                        result.Add(instance);
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 获取语言下所有界面
        /// </summary>
        /// <returns></returns>
        public List<PageRefInstance> GetPageRefInstancesByBranchID(string branchID)
        {
            var result = new List<PageRefInstance>();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>();
            paramList.Add(DB.MakeInParam("@branchID", TDbDataType.VarChar, branchID));
            var sql = $@"select ID,Code from meta_basicinfo where MetaTypeID in( 'Page','UIControl','Portlet') and ProjectBranchID = @branchID";
            var ds = DB.ExecuteDataSet(sql, paramList.ToArray());
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var row = ds.Tables[0].Rows[i];
                    var instance = new PageRefInstance();
                    instance.PageMetaDataID = Convert.ToString(row["ID"]);
                    instance.PageMetaDataCode = Convert.ToString(row["Code"]);
                    result.Add(instance);
                }
            }
            return result;
        }
        /// <summary>
        /// 获取语言下所有界面
        /// </summary>
        /// <returns></returns>
        public List<PageRefInstance> GetPageRefInstancesByTermID(List<string> termIDs)
        {
            var result = new List<PageRefInstance>();
            if (termIDs.Count > 0)
            {
                List<IDbDataParameter> paramList = new List<IDbDataParameter>();
                var sql = $@"select distinct meta.ID,meta.Code from fas_pagemetadata meta
                                    left join fas_pagerefschema refschema on meta.id = refschema.PageMetaDataID
                                    left join Page_TermMapping trans on (trans.SourceID = refschema.PageMetaDataID or trans.SourceID = refschema.MetaDataID)
                                    where trans.TermID in ({GetIDFilter(termIDs)})";
                var ds = DB.ExecuteDataSet(sql, paramList.ToArray());
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        var row = ds.Tables[0].Rows[i];
                        var instance = new PageRefInstance();
                        instance.PageMetaDataID = Convert.ToString(row["ID"]);
                        instance.PageMetaDataCode = Convert.ToString(row["Code"]);
                        result.Add(instance);
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 获取语言下所有界面
        /// </summary>
        /// <returns></returns>
        public List<PageRefInstance> GetPageRefInstancesByTermMapping(List<string> termMappingIDs)
        {
            var result = new List<PageRefInstance>();
            if (termMappingIDs.Count > 0)
            {
                List<IDbDataParameter> paramList = new List<IDbDataParameter>();
                var sql = $@"select distinct meta.ID,meta.Code from fas_pagemetadata meta
                                    left join fas_pagerefschema refschema on meta.id = refschema.PageMetaDataID
                                    left join Page_TermMapping trans on (trans.SourceID = refschema.PageMetaDataID or trans.SourceID = refschema.MetaDataID)
                                    where trans.ID in ({GetIDFilter(termMappingIDs)})";
                var ds = DB.ExecuteDataSet(sql, paramList.ToArray());
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        var row = ds.Tables[0].Rows[i];
                        var instance = new PageRefInstance();
                        instance.PageMetaDataID = Convert.ToString(row["ID"]);
                        instance.PageMetaDataCode = Convert.ToString(row["Code"]);
                        result.Add(instance);
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 修改词条信息
        /// </summary>
        /// <param name="i18NPageTerms"></param>
        public void UpdateTermMappings(List<I18NPageTermMapping> i18NPageTerms)
        {
            List<string> sqls = new List<string>();
            List<IDbDataParameter[]> paramList = new List<IDbDataParameter[]>();
            string userId = UserUtil.GetCurrentUserID();
            foreach (var mapping in i18NPageTerms)
            {
                if (!string.IsNullOrEmpty(mapping.CustomTermID))
                {
                    List<IDbDataParameter> paramLists = new List<IDbDataParameter>();
                    paramLists.Add(DB.MakeInParam("@termID", TDbDataType.VarChar, mapping.TermID));
                    paramLists.Add(DB.MakeInParam("@customTermID", TDbDataType.VarChar, mapping.CustomTermID));
                    paramLists.Add(DB.MakeInParam("@mappingsID", TDbDataType.VarChar, mapping.ID));
                    paramLists.Add(DB.MakeInParam("@lastModifier", TDbDataType.VarChar, userId));
                    var sql = "Update I18N_Term set LastModifyTime=now() where ID=@termID; Update Page_TermMapping set CustomTermID=@customTermID,LastModifyTime=now(),LastModifier=@lastModifier where ID=@mappingsID;";
                    sqls.Add(sql);
                    paramList.Add(paramLists.ToArray());
                }
                foreach (var item in mapping.TranslateItems)
                {
                    if (string.IsNullOrEmpty(item.CustomText))
                        continue;
                    List<IDbDataParameter> paramLists = new List<IDbDataParameter>();
                    paramLists.Add(DB.MakeInParam("@termID", TDbDataType.VarChar, mapping.TermID));
                    paramLists.Add(DB.MakeInParam("@customText", TDbDataType.VarChar, item.CustomText));
                    paramLists.Add(DB.MakeInParam("@language", TDbDataType.VarChar, item.Language));
                    paramLists.Add(DB.MakeInParam("@lastModifier", TDbDataType.VarChar, userId));
                    var sql = "Update Page_TermMapping set LastModifyTime=now() where TermID=@termID; Update I18N_TermItems set CustomText=@customText,LastModifyTime=now(),LastModifier=@lastModifier where Language=@language AND TermID=@termID;";
                    sqls.Add(sql);
                    paramList.Add(paramLists.ToArray());
                }
            }
            base.DB.ExecSqlStatement(sqls.ToArray(), paramList.ToArray());
        }
        /// <summary>
        /// 处理
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        private static string GetIDFilter(List<string> idList)
        {
            // 只能包含数字、字母、_、-和.
            string pattern = @"^[a-zA-Z0-9_\-.]+$";
            StringBuilder condition = new StringBuilder();
            foreach (var id in idList)
            {
                if (!Regex.IsMatch(id, pattern))
                {
                    continue;
                }
                condition.AppendFormat("'{0}',", id);
            }
            if (condition.Length > 0)
            {
                condition.Remove(condition.Length - 1, 1);
            }
            else
            {
                condition.Append("''");
            }

            return condition.ToString();
        }

        #region 临时
        public List<I18NPageTermMapping> GetByCode(List<string> codes)
        {
            string condition = GetIDFilter(codes);
            var sql = $"select p.TermID,p.Code,p.CustomTermID,t.code TermCode from {TableName} p INNER JOIN i18n_term t on p.termid=t.id   where p.code in ({condition})";
            List<I18NPageTermMapping> items = new List<I18NPageTermMapping>();
            var ds = DB.ExecuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var row = ds.Tables[0].Rows[i];
                    var mapping = new I18NPageTermMapping();
                    mapping.TermID = Convert.ToString(row["TermID"]);
                    mapping.Code = Convert.ToString(row["Code"]);
                    mapping.TermCode = Convert.ToString(row["TermCode"]);
                    mapping.CustomTermID = Convert.ToString(row["CustomTermID"]);
                    items.Add(mapping);
                }
            }
            return items;
        }
        public List<T.ServiceComponent.I18N.SPI.I18NTermItem> GetByTermId(string termId)
        {
            List<IDbDataParameter> paramLists = new List<IDbDataParameter>();
            paramLists.Add(DB.MakeInParam("@termId", TDbDataType.VarChar, termId));
            var sql = $"select ID,Language,TranslateText,CustomText from I18N_TermItems where TermId =@termId;";
            List<T.ServiceComponent.I18N.SPI.I18NTermItem> items = new List<T.ServiceComponent.I18N.SPI.I18NTermItem>();
            var ds = DB.ExecuteDataSet(sql,paramLists.ToArray());
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var row = ds.Tables[0].Rows[i];
                    var mapping = new T.ServiceComponent.I18N.SPI.I18NTermItem();
                    mapping.ID = Convert.ToString(row["ID"]);
                    mapping.Language = Convert.ToString(row["Language"]);
                    mapping.TranslateText = Convert.ToString(row["TranslateText"]);
                    mapping.CustomText = Convert.ToString(row["CustomText"]);
                    items.Add(mapping);
                }
            }
            return items;
        }
        #endregion
    }
}
